﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Connect
{
    public partial class Import : Form
    {
        public Import()
        {
            InitializeComponent();
        }
        SqlConnection Con;
        string DuongDanExcel = "";
        string TableName = "";
        string Sheet = "";
        DataTable TableSQL;
        DataTable TableExcel;
        public Import(string TableName)
        {
            InitializeComponent();
            this.TableName = TableName;
        }        

        public Import(SqlConnection con,string TableName)
        {
            InitializeComponent();
            this.Con = con;
            this.TableName = TableName;
        }
       
      
        private void Import_Load(object sender, EventArgs e)
        {
            cbSQL.DataSource =   Connect.Excel.ListTableSQL();
            cbSQL.ValueMember = "name";
            cbSQL.DisplayMember = "name";
            cbSQL_SelectedIndexChanged(sender, e);
            cbLuaChonNangCao.SelectedIndex = 0;
        }

        private void btnTim_Click(object sender, EventArgs e)
        {
            MoFileExcel.ShowDialog();
            if (string.IsNullOrEmpty(MoFileExcel.FileName.ToString())==true)
            {
                txtExcel.Focus();
                return;
            }
            DuongDanExcel = MoFileExcel.FileName;
            txtExcel.Text = MoFileExcel.SafeFileName;
            // Load danh sách các Sheet
            Excel Ex = new Excel(DuongDanExcel);
            cbSheet.DataSource = Ex.Sheet();
            cbSheet.Sorted = false;
            cbSheet.DisplayMember = "TABLE_NAME";
            cbSheet.ValueMember = "TABLE_NAME";
            if (cbSheet.Items.Count>0)
            {
                cbSheet.SelectedIndex = 0;
                cbSheet_SelectedIndexChanged(sender, e);
             }
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtExcel.Text.Trim())==true)
            {
                return;
            }
            Connect.Excel Excel = new Excel(DuongDanExcel);
            DataTable temp = Excel.ExcelTable(cbSheet.SelectedValue.ToString().Trim());
            ViewExcel f = new ViewExcel(temp);
            f.ShowDialog();
        }

        private void cbSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
           {

                        Sheet = cbSheet.SelectedValue.ToString().Trim();
                        // Load dữ liệu của Sheet lên bảng
                        Connect.Excel Excel = new Excel(DuongDanExcel);
                        DataTable temp = Excel.ExcelTable(Sheet);
                        cbSheet_SelectedValueChanged(sender, e);
            }
            catch (Exception)
            {
                
    
            }
        
        }

        private void cbSheet_DataSourceChanged(object sender, EventArgs e)
        {
         btnExcel.Enabled =  !(cbSheet.Items.Count > 0);
        }

        private void btnSQL_Click(object sender, EventArgs e)
        {
            DataTable temp = Connect.cnSQL.ExecQuery("Select * from " + TableName);
            ViewSQL f = new ViewSQL(temp);
            f.ShowDialog();
        }

        private void cbSheet_SelectedValueChanged(object sender, EventArgs e)
        {
            try
            {
                for (int i = 0; i < dgvDanhSach.Columns.Count; i++)
                {
                    if (dgvDanhSach.Columns[i].Name.ToString() == "Database")
                    {
                        dgvDanhSach.Columns.Remove("Database");
                    }
                }
                  Connect.Excel Excel = new Excel(DuongDanExcel);
                  DataTable temp = Excel.ExcelTable(Sheet.Trim());
                    Excel.TruongDuLieuExcel(temp,dgvDanhSach);
               
                  if (temp != null)
                  {
                      System.Windows.Forms.DataGridViewComboBoxColumn CSDL = new System.Windows.Forms.DataGridViewComboBoxColumn();
                      CSDL.HeaderText = "Database";
                      CSDL.Name = "Database";
                      dgvDanhSach.Columns.AddRange(new System.Windows.Forms.DataGridViewComboBoxColumn[] {CSDL});
                      DataTable tempsql = Excel.TruongSQL(TableName);
                      CSDL.DataSource = tempsql;
                      CSDL.DisplayMember = "column_name";
                      CSDL.ValueMember = "column_name";
                        

                  }
                  else
                  {  

                      for (int i = 0; i < dgvDanhSach.Columns.Count; i++)
                          {

                              if (dgvDanhSach.Columns[i].Name.ToString() == "CSDL")
                              {
                                  dgvDanhSach.Columns.Remove("CSDL");
                              }

                          }
                 
                  }
            }
            catch (Exception)
            {

            }
            dgvDanhSach.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
      
        }

        private void cbDongBatDau_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                cbSheet_SelectedValueChanged(sender, e);    
            }
            catch (Exception)
            {

            }

        }

        private void cbSQL_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                TableName = cbSQL.SelectedValue.ToString();
                TableSQL = Excel.TruongSQL(TableName);
                cbSheet_SelectedValueChanged(sender, e);    
            }
            catch (Exception)
            {

            }
        }

        private void dgvDanhSach_CellValidated(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (dgvDanhSach.Rows[e.RowIndex].Cells["Database"].Value != null)
                    {
                        dgvDanhSach.Rows[e.RowIndex].Cells["Chon"].Value = true;
                    }
                    else
                    {
                        dgvDanhSach.Rows[e.RowIndex].Cells["Chon"].Value = false;
                    }

            }
            catch (Exception)
            {
                    cbSheet_SelectedValueChanged(sender, e);    
            }

     
       
        }

        private void chkKhoaChinh_CheckedChanged(object sender, EventArgs e)
        {
            txtDauNgu.Enabled = txtDinhDang.Enabled = (chkKhoaChinh.Checked);
        }

        string ScriptSQL = "Insert into " ;
           string DungSai(string s)
        {
            // Nam - Nữ
            if (s.ToLower().Contains("nam") == true)
            {
                return "True";
            }
            else
            {
                return "False";
            }
            // 1 - 0
            if (s.ToLower().Contains("1") == true)
            {
                return "True";
            }
            else
            {
                return "False";
            }
            // True - False
            if (s.ToLower().Contains("True") == true)
            {
                return "True";
            }
            else
            {
                return "False";
            }
        }
        string ColumnInsert()
        {
            string sql = "";
            string temp = "";
            if (chkKhoaChinh.Checked == true)
            {
                sql = ScriptSQL + TableName + "(" + TableSQL.Rows[0]["column_name"].ToString() + ", ";
            }
            else
            {
                sql = ScriptSQL + TableName + "(";
            }
            for (int i = 0; i < dgvDanhSach.RowCount; i++)
            {
                try
                {
                        if (((bool)dgvDanhSach.Rows[i].Cells["Chon"].Value==true)&&((object)dgvDanhSach.Rows[i].Cells["Database"].Value!=null))
                        {
                            temp = temp + dgvDanhSach.Rows[i].Cells["Database"].Value + ", ";
                        }
                }
                catch (Exception)
                {
     
                }
             
            }
            temp = sql + temp + ")";
            return temp.Replace(", )", ")") + " values(";
        }
        void ExecuteSQL()
        {   
            string InsertScript = ColumnInsert() ;
            Connect.Excel Excel = new Excel(DuongDanExcel);
            DataTable temp = Excel.ExcelTable(Sheet);
            // Lựa chọn chức năng Bổ sung thêm thông tin dữ liệu
            if (cbLuaChonNangCao.SelectedIndex==0)
	                {
                        if (chkKhoaChinh.Checked == true)
                        {
                            #region Mã tùy chọn
                            
                            
                            //http://www.sucaitianxia.com/
                            string sql = "";
                            for (int i = 0; i < temp.Rows.Count; i++)
                            {
                                //////////////////////////
                                string dong = "";
                                for (int j = 0; j < dgvDanhSach.Rows.Count; j++)
                                {
                                    try
                                    {
                                        if (((bool)dgvDanhSach.Rows[j].Cells["Chon"].Value == true) && (dgvDanhSach.Rows[j].Cells["Database"].Value != null))
                                        {
                                            // Điều kiện kiểu dữ liệu
                                            if (string.IsNullOrEmpty(temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString()) == true)

                                                dong = dong + ",NULL,";

                                            if (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.String")
                                                dong = dong + "N'" + temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString() + "',";
                                            if (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Int32")
                                                dong = dong + "'" + Math.Round(decimal.Parse(temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString())) + "',";
                                            if ((temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Decimal") || (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Double") || (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Single"))
                                                dong = dong + "'" + temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString() + "',";
                                            if (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.DateTime")
                                            {
                                                // MessageBox.Show(DateTime.FromOADate(33030).ToString());
                                                dong = dong + "convert(datetime,'" + temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString() + "',103),";
                                            }
                                            if (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Boolean")
                                            {
                                                //MessageBox.Show(DungSai(temp.Rows[i][j].ToString()));
                                                dong = dong + "'" + DungSai(temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString()) + "',";
                                            }
                                        }


                                    }
                                    catch (Exception)
                                    {


                                    }


                                }


                                sql = (InsertScript + "'" + Excel.Sinh(txtDauNgu.Text.Trim(),cnSQL.ExecQuery("Select * from " + TableName),txtDinhDang.Text.Trim()) + "'," +dong + ")").Replace(", ,", " , ").Replace(",,", "'").Replace(",,NULL,", ",NULL,").Replace(",)", ")").Replace("'NULL", ",NULL");
                                ///////////////////////////////////////
                               cnSQL.ExecNonQuery(sql);
                           
                            }
                            #endregion
                        }
                        // Không có khóa chính hay Mã để tăng tự động
                        else
                        {
                            #region Mã tư động
                            
                            
                            //http://www.sucaitianxia.com/
                            string sql = "";
                            for (int i = 0; i < temp.Rows.Count; i++)
                            {
                                //////////////////////////
                                string dong = "";
                                for (int j = 0; j < dgvDanhSach.Rows.Count; j++)
                                {
                                    try
                                    {
                                        if (((bool)dgvDanhSach.Rows[j].Cells["Chon"].Value == true) && (dgvDanhSach.Rows[j].Cells["Database"].Value != null))
                                        {
                                            // Điều kiện kiểu dữ liệu
                                            if (string.IsNullOrEmpty(temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString()) == true)

                                                dong = dong + ",NULL,";

                                            if (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.String")
                                                dong = dong + "N'" + temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString() + "',";
                                            if (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Int32")
                                                dong = dong + "'" + Math.Round(decimal.Parse(temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString())) + "',";
                                            if ((temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Decimal") || (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Double") || (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Single"))
                                                dong = dong + "'" + temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString() + "',";
                                            if (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.DateTime")
                                            {
                                                // MessageBox.Show(DateTime.FromOADate(33030).ToString());
                                                dong = dong + "convert(datetime,'" + temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString() + "',103),";
                                            }
                                            if (temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].GetType().ToString() == "System.Boolean")
                                            {
                                                //MessageBox.Show(DungSai(temp.Rows[i][j].ToString()));
                                                dong = dong + "'" + DungSai(temp.Rows[i][dgvDanhSach.Rows[j].Cells["Excel"].Value.ToString()].ToString()) + "',";
                                            }
                                        }


                                    }
                                    catch (Exception)
                                    {


                                    }


                                }


                                sql = (InsertScript +  dong + ")").Replace(", ,", " , ").Replace(",,", "'").Replace(",,NULL,", ",NULL,").Replace(",)", ")").Replace("'NULL",",NULL") ;
                                ///////////////////////////////////////
                               cnSQL.ExecNonQuery(sql);
                            }
 #endregion
                        }
                           
	                }
            // Lựa chọn chức năng Sửa đổi thông tin đã tồn tại
            else
                    {
                                // Dựa vào giá trị của trường nào trong dữ liệu để sửa 


                    }
        }

        private void btnThucHien_Click(object sender, EventArgs e)
        {
            ExecuteSQL();
            Close();
        }

        private void btnHuyBo_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void cbLuaChonNangCao_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtDauNgu.Enabled = txtDinhDang.Enabled = chkKhoaChinh.Checked = chkKhoaChinh.Enabled = (cbLuaChonNangCao.SelectedIndex == 0) ? true : false;
        }

  


    }
}
